﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using CustomsSystem.Model;
using Maticsoft.DBUtility;

namespace CustomsSystem.DAL
{
    public class ContactService
    {
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Cu_Contact");
            strSql.Append(" where ID=" + ID + " ");
            return DbHelperSQL.Exists(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(CustomsSystem.Model.Contact model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Cu_Contact(");
            strSql.Append("Customer_ID,CPName,CPSex,CPPost,MobilePhone,CPPhone,Fax,Email,Remarks)");
            strSql.Append(" values (");
            strSql.Append(" @Customer,@CPName,@CPSex,@CPPost,@MobilePhone,@CPPhone,@Fax,@Email,@Remarks)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Customer", SqlDbType.Int,4),
					new SqlParameter("@CPName", SqlDbType.VarChar,20),
					new SqlParameter("@CPSex", SqlDbType.VarChar,1),
					new SqlParameter("@CPPost", SqlDbType.VarChar,20),
					new SqlParameter("@MobilePhone", SqlDbType.VarChar,11),
					new SqlParameter("@CPPhone", SqlDbType.VarChar,20),
					new SqlParameter("@Fax", SqlDbType.VarChar,20),
					new SqlParameter("@Email", SqlDbType.VarChar,50),
					new SqlParameter("@Remarks", SqlDbType.VarChar,200)};
            parameters[0].Value = model.Customer_ID;
            parameters[1].Value = model.CPName;
            parameters[2].Value = model.CPSex;
            parameters[3].Value = model.CPPost;
            parameters[4].Value = model.MobilePhone;
            parameters[5].Value = model.CPPhone;
            parameters[6].Value = model.Fax;
            parameters[7].Value = model.Email;
            parameters[8].Value = model.Remarks;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(CustomsSystem.Model.Contact model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Cu_Contact set ");
            strSql.Append("Customer_ID= @Customer,");
            strSql.Append("CPName= @CPName,");
            strSql.Append("CPSex= @CPSex,");
            strSql.Append("CPPost= @CPPost,");
            strSql.Append("MobilePhone= @MobilePhone,");
            strSql.Append("CPPhone= @CPPhone,");
            strSql.Append("Fax= @Fax,");
            strSql.Append("Email= @Email,");
            strSql.Append("Remarks= @Remarks");
            strSql.Append(" where ID= @ID");
            SqlParameter[] parameters = {
					new SqlParameter("@Customer", SqlDbType.Int,4),
					new SqlParameter("@CPName", SqlDbType.VarChar,20),
					new SqlParameter("@CPSex", SqlDbType.VarChar,1),
					new SqlParameter("@CPPost", SqlDbType.VarChar,20),
					new SqlParameter("@MobilePhone", SqlDbType.VarChar,11),
					new SqlParameter("@CPPhone", SqlDbType.VarChar,20),
					new SqlParameter("@Fax", SqlDbType.VarChar,20),
					new SqlParameter("@Email", SqlDbType.VarChar,50),
					new SqlParameter("@Remarks", SqlDbType.VarChar,200),
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = model.Customer_ID;
            parameters[1].Value = model.CPName;
            parameters[2].Value = model.CPSex;
            parameters[3].Value = model.CPPost;
            parameters[4].Value = model.MobilePhone;
            parameters[5].Value = model.CPPhone;
            parameters[6].Value = model.Fax;
            parameters[7].Value = model.Email;
            parameters[8].Value = model.Remarks;
            parameters[9].Value = model.ID;

            int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int ID)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("delete from Cu_Contact ");
            strSql.Append(" where ID=" + ID + " ");
            int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CustomsSystem.Model.Contact GetModel(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1  ");
            strSql.Append(" ID,Customer_ID,CPName,CPSex,CPPost,MobilePhone,CPPhone,Fax,Email,Remarks ");
            strSql.Append(" from Cu_Contact ");
            strSql.Append(" where ID=" + ID + " ");
            CustomsSystem.Model.Contact model = new CustomsSystem.Model.Contact();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CustomsSystem.Model.Contact DataRowToModel(DataRow row)
        {
            CustomsSystem.Model.Contact model = new CustomsSystem.Model.Contact();
            if (row != null)
            {
                if (row["ID"] != null && row["ID"].ToString() != "")
                {
                    model.ID = int.Parse(row["ID"].ToString());
                }
                if (row["Customer_ID"] != null && row["Customer_ID"].ToString() != "")
                {
                    model.Customer_ID = int.Parse(row["Customer_ID"].ToString());
                }
                if (row["CPName"] != null)
                {
                    model.CPName = row["CPName"].ToString();
                }
                if (row["CPSex"] != null)
                {
                    model.CPSex = row["CPSex"].ToString();
                }
                if (row["CPPost"] != null)
                {
                    model.CPPost = row["CPPost"].ToString();
                }
                if (row["MobilePhone"] != null)
                {
                    model.MobilePhone = row["MobilePhone"].ToString();
                }
                if (row["CPPhone"] != null)
                {
                    model.CPPhone = row["CPPhone"].ToString();
                }
                if (row["Fax"] != null)
                {
                    model.Fax = row["Fax"].ToString();
                }
                if (row["Email"] != null)
                {
                    model.Email = row["Email"].ToString();
                }
                if (row["Remarks"] != null)
                {
                    model.Remarks = row["Remarks"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Cu_Contact.ID,Customer_ID,CPName,CPSex=case CPSex when 0 then '男' when 1 then '女' END ,CPPost,MobilePhone,CPPhone,Fax,Cu_Contact.Email,Cu_Contact.Remarks,Cu_Customer.CustomerName ");
            strSql.Append(" FROM Cu_Contact,Cu_Customer ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where Customer_ID = Cu_Customer.ID and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM Cu_Contact ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        //===========================================================================

       /* public static DataSet GetContactDataSet()
        {
            DataSet ds = new DataSet();
            try
            {
                DBHelper.conn.Open();
                string strCmd = string.Format("select * from Cu_Contact");
                ds = DBHelper.ExecuteDataSet(strCmd);
            }
            catch (Exception ex) { }
            finally
            {
                DBHelper.conn.Close();
            }
            return ds;
        }

        public static DataSet GetContactDataSet(string id)
        {
            DataSet ds = new DataSet();
            try
            {
                DBHelper.conn.Open();
                string strCmd = string.Format("select * from Cu_Contact where ID='{0}'", id);
                //if (DBHelper.SqlFilter(strCmd)) return null;//条件过滤
                ds = DBHelper.ExecuteDataSet(strCmd);
            }
            catch (Exception ex) { }
            finally
            {
                DBHelper.conn.Close();
            }
            return ds;
        }
        /// <summary>
        /// 插入一个联系人信息
        /// </summary>
        /// <param name="cus"></param>
        /// <returns></returns>
        public static bool InsertContact(Contact con)
        {
            try
            {
                DBHelper.conn.Open();
                string strCmd = string.Format
                    ("INSERT INTO Cu_Contact(Customer_ID,CPName,CPSex,CPPost,MobilePhone,CPPhone,Remarks) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
                   con.Customer_ID,con.CPName,con.CPSex,con.CPPost,con.MobilePhone,con.CPPhone,con.Remarks );
                int n = DBHelper.ExecuteNonQuery(strCmd);
                if (n == -1) return false;
                else return true;
            }
            catch (Exception ex) { }
            finally { DBHelper.conn.Close(); }
            return false;
        }

        /// <summary>
        /// 更新一个联系人信息
        /// </summary>
        /// <param name="cus"></param>
        /// <returns></returns>
        public static bool UpdateContact(Contact con)
        {
            try
            {
                DBHelper.conn.Open();
                string strCmd = string.Format(
                    "UPDATE Cu_Contact SET Customer_ID='{0}',CPName='{1}',CPSex='{2}',CPPost='{3}',MobilePhone='{4}',CPPhone='{5}',Remarks='{6}' WHERE ID='{7}'",
                     con.Customer_ID, con.CPName, con.CPSex, con.CPPost, con.MobilePhone, con.CPPhone, con.Remarks,con.ID);
                int n = DBHelper.ExecuteNonQuery(strCmd);
                if (n == -1) return false;
                else return true;
            }
            catch (Exception ex) { }
            finally { DBHelper.conn.Close(); }
            return false;
        }
        /// <summary>
        /// 删除一个联系人信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DelContac(string id)
        {
            try
            {
                DBHelper.conn.Open();
                string strCmd = string.Format(
                    "DELETE FROM Cu_Contact WHERE ID='{0}'", id);
                int n = DBHelper.ExecuteNonQuery(strCmd);
                if (n == -1) return false;
                else return true;
            }
            catch (Exception ex) { }
            finally { DBHelper.conn.Close(); }
            return false;
        }*/
        
    }
}
